﻿using System;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;

namespace LMHTX.Modules.DIRECTORY.COOPERATIVE.Components
{
    public partial class Cooperative
    {
        public static DataSet Search(Guid moduleID, string whereCondition)
        {
            const string spName = "[dbo].[p_COOPERATIVE_Cooperative_Search]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, moduleID);
            db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);

            return db.ExecuteDataSet(dbCommand);
        }

        public static void Summary(Guid moduleID, int year, string areaID, string typeID, string careerID,
            ref int CountOfCooperative,
            ref int SumOfLabourQuantity,
            ref int SumOfWomanLabourQuantity,
            ref int SumOfCooperatorQuantity,
            ref int SumOfWomanCooperatorQuantity,
            ref decimal SumOfCharterCapital,
            ref decimal SumOfWorkingCapital,
            ref decimal SumOfFixedCapital,
            ref decimal SumOfProfit,
            ref decimal SumOfTax,
            ref decimal SumOfTurnover,
            ref int SumOfCooperativePartyMember,
            ref int CountOfCooperativeHasPartyOrganization,
            ref int CountOfCooperativeHasTradeUnion,
            ref int CountOfCooperativeHasTheCommunistYouthLeague,
            ref int CountOfCooperativeHasWomanUnion,
            ref int CountOfCooperativeHasInlandMarket,
            ref int CountOfCooperativeHasOutlandMarket)
        {

            const string spName = "[dbo].[p_COOPERATIVE_Cooperative_Statistics_Summary]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, moduleID);
            db.AddInParameter(dbCommand, "@Year", SqlDbType.Int, year);
            db.AddInParameter(dbCommand, "@AreaID", SqlDbType.VarChar, areaID);
            db.AddInParameter(dbCommand, "@TypeID", SqlDbType.VarChar, typeID);
            db.AddInParameter(dbCommand, "@CareerID", SqlDbType.VarChar, careerID);

            db.AddOutParameter(dbCommand, "@CountOfCooperative", SqlDbType.Int, 4);
            db.AddOutParameter(dbCommand, "@SumOfLabourQuantity", SqlDbType.Int, 4);
            db.AddOutParameter(dbCommand, "@SumOfWomanLabourQuantity", SqlDbType.Int, 4);
            db.AddOutParameter(dbCommand, "@SumOfCooperatorQuantity", SqlDbType.Int, 4);
            db.AddOutParameter(dbCommand, "@SumOfWomanCooperatorQuantity", SqlDbType.Int, 4);
            db.AddOutParameter(dbCommand, "@SumOfCharterCapital", SqlDbType.Money, 128);
            db.AddOutParameter(dbCommand, "@SumOfWorkingCapital", SqlDbType.Money, 128);
            db.AddOutParameter(dbCommand, "@SumOfFixedCapital", SqlDbType.Money, 128);
            db.AddOutParameter(dbCommand, "@SumOfProfit", SqlDbType.Money, 128);
            db.AddOutParameter(dbCommand, "@SumOfTax", SqlDbType.Money, 128);
            db.AddOutParameter(dbCommand, "@SumOfTurnover", SqlDbType.Money, 128);
            db.AddOutParameter(dbCommand, "@SumOfCooperativePartyMember", SqlDbType.Int, 4);
            db.AddOutParameter(dbCommand, "@CountOfCooperativeHasPartyOrganization", SqlDbType.Int, 4);
            db.AddOutParameter(dbCommand, "@CountOfCooperativeHasTradeUnion", SqlDbType.Int, 4);
            db.AddOutParameter(dbCommand, "@CountOfCooperativeHasTheCommunistYouthLeague", SqlDbType.Int, 4);
            db.AddOutParameter(dbCommand, "@CountOfCooperativeHasWomanUnion", SqlDbType.Int, 4);
            db.AddOutParameter(dbCommand, "@CountOfCooperativeHasInlandMarket", SqlDbType.Int, 4);
            db.AddOutParameter(dbCommand, "@CountOfCooperativeHasOutlandMarket", SqlDbType.Int, 4);

            
            db.ExecuteNonQuery(dbCommand);

            CountOfCooperative = (int)db.GetParameterValue(dbCommand, "@CountOfCooperative");
            
            object o = db.GetParameterValue(dbCommand, "@SumOfLabourQuantity");
            SumOfLabourQuantity = o == DBNull.Value ? 0 : (int) o;

            o = db.GetParameterValue(dbCommand, "@SumOfWomanLabourQuantity");
            SumOfWomanLabourQuantity = o == DBNull.Value ? 0 : (int)o;

            o = db.GetParameterValue(dbCommand, "@SumOfCooperatorQuantity");
            SumOfCooperatorQuantity = o == DBNull.Value ? 0 : (int)o;

            o = db.GetParameterValue(dbCommand, "@SumOfWomanCooperatorQuantity");
            SumOfWomanCooperatorQuantity = o == DBNull.Value ? 0 : (int)o;

            o = db.GetParameterValue(dbCommand, "@SumOfCharterCapital");
            SumOfCharterCapital = o == DBNull.Value ? 0 : (decimal)o;

            o = db.GetParameterValue(dbCommand, "@SumOfWorkingCapital");
            SumOfWorkingCapital = o == DBNull.Value ? 0 : (decimal)o;

            o = db.GetParameterValue(dbCommand, "@SumOfFixedCapital");
            SumOfFixedCapital = o == DBNull.Value ? 0 : (decimal)o;

            o = db.GetParameterValue(dbCommand, "@SumOfProfit");
            SumOfProfit = o == DBNull.Value ? 0 : (decimal)o;

            o = db.GetParameterValue(dbCommand, "@SumOfTax");
            SumOfTax = o == DBNull.Value ? 0 : (decimal)o;

            o = db.GetParameterValue(dbCommand, "@SumOfTurnover");
            SumOfTurnover = o == DBNull.Value ? 0 : (decimal)o;

            o = db.GetParameterValue(dbCommand, "@SumOfCooperativePartyMember");
            SumOfCooperativePartyMember = o == DBNull.Value ? 0 : (int)o;

            CountOfCooperativeHasPartyOrganization = (int)db.GetParameterValue(dbCommand, "@CountOfCooperativeHasPartyOrganization");
            CountOfCooperativeHasTradeUnion = (int)db.GetParameterValue(dbCommand, "@CountOfCooperativeHasTradeUnion");
            CountOfCooperativeHasTheCommunistYouthLeague = (int)db.GetParameterValue(dbCommand, "@CountOfCooperativeHasTheCommunistYouthLeague");
            CountOfCooperativeHasWomanUnion = (int)db.GetParameterValue(dbCommand, "@CountOfCooperativeHasWomanUnion");
            CountOfCooperativeHasInlandMarket = (int)db.GetParameterValue(dbCommand, "@CountOfCooperativeHasInlandMarket");
            CountOfCooperativeHasOutlandMarket = (int)db.GetParameterValue(dbCommand, "@CountOfCooperativeHasOutlandMarket");
        }
    }
}
